生成AIにスプレッドシートの集計方法を訪ねてみた結果

生成AIにスプレッドシートの集計方法を訪ねてみた結果

ちょっとした業務効率化が出来た記事です。
Clock Icon2024.05.31

こんにちは、高崎@アノテーション です。

はじめに

スプレッドシートで業務関連の集計をしておりまして、効率的な集計はないものか、と弊社 Slack に導入されている生成 AI bot にたずねてみたらすんなり解決した話を記事にします。

Slack に導入されている生成 AI bot については下記のブログをご参照ください。

前提

こんな感じの集計表があったとして(詳細を記載すると NDA 抵触する可能性がありますので非常にざっくりした表ですが)、集計方法の遷移を順次記載していきます。

集計方法

1. 最初の集計方法

このシートとは別に建てたシート(例:「集計」)に品目の合計を計算する。

というのが最初の要件でした。

なお、「集計」シートは下記のような感じです。

ここは Excel でも経験があるので、そんなに難しくはなさそうで、

=SUMIF('記録'!B:B, B3, '記録'!C:C)

として最初の行はりんごを集計、その後下2つコピペして、

こんな感じで集計できますね。

2. 追加の集計要件

「集計」シートに品目の合計を計算するが、対象担当が記載されていないものは計算しない。

「集計」シートはこんなレイアウトです。

E 列に対象メンバーを記入し、記入されたメンバー限定で集計したい、というものです。

当初考えていたのが、SUMIFS を使って

=SUMIFS('記録'!C:C,'記録'!B:B, B3, '記録'!D:D, $E$3)+SUMIFS('記録'!C:C,'記録'!B:B, B3, '記録'!D:D, $E$4)

という感じなのですが、対象メンバーが増えるごとに書き足しが面倒ですし、いっそ、

=SUMIFS('記録'!C:C,'記録'!B:B, B3, '記録'!D:D, $E$3)+SUMIFS('記録'!C:C,'記録'!B:B, B3, '記録'!D:D, $E$4)+SUMIFS('記録'!C:C,'記録'!B:B, B3, '記録'!D:D, $E$5)+SUMIFS('記録'!C:C,'記録'!B:B, B3, '記録'!D:D, $E$6)+SUMIFS('記録'!C:C,'記録'!B:B, B3, '記録'!D:D, $E$7)+SUMIFS('記録'!C:C,'記録'!B:B, B3, '記録'!D:D, $E$8)+…

と、増加されるメンバーを多めに設定するという考えもそれ以上の対象メンバーが増えたときは一緒だし、良い感じのエレガントな集約方法は無いものか、と考えておりました。

いつもなら google センセイにお伺いするのですが、今回は冒頭に書いた 生成 AI 機能を持つ Slack ボットに聞いてみようと思い、色々と会話してみました。

ほぉ。

=SUM(FILTER(C2:C9, (A2:A9="りんご") * (REGEXMATCH(B2:B9, TEXTJOIN("|", true, F2:F)))))

という提案を頂きましたので、これをちょっと改良し、

=SUM(FILTER('記録'!C:C, ('記録'!B:B=B3) * (REGEXMATCH('記録'!D:D, TEXTJOIN("|", true, E:E)))))

で実行。

うまくいきました。

これに E 列にメンバーを追加すると、

こんな感じで値が変わりますし、一方でメンバーを減らすとこんな感じで減る…

(´・ω・`)
あり。

FILTERでエラーになってそのまま表示出来なかったので、ISERRORを使って少し長くなりますが、

=IF(ISERROR(FILTER('記録'!C:C, ('記録'!B:B=B3) * (REGEXMATCH('記録'!D:D, TEXTJOIN("|", true, E:E))))), 0, SUM(FILTER('記録'!C:C, ('記録'!B:B=B3) * (REGEXMATCH('記録'!D:D, TEXTJOIN("|", true, E:E))))))

FILTERのエラーを検知してやれば、

うまく行きましたね。

おまけの要件

「記録」シートを月ごとに整理したい

「記録」シートを例えば2024-05記録みたいなシート名にして月ごとに集計したい場合、シート名を単純に変えると上述の式のシート名の記載が変わってくれるのですが、月ごとに2024-05記録 2024-06記録…としたい時に、

この対象月のセル(赤枠)を変更しただけで集計したい、というものです。

毎回月が変わるごとにそれぞれのセルを対象月のシート名に変えるのは面倒ですよね?

ここは AI に尋ねるまでもなくINDIRECTを使えば解決。

=IF(ISERROR(FILTER(INDIRECT(TEXT($C$2,"yyyy-mm")&"記録!C:C"), (INDIRECT(TEXT($C$2,"yyyy-mm")&"記録!B:B")=B5) * (REGEXMATCH(INDIRECT(TEXT($C$2,"yyyy-mm")&"記録!D:D"),TEXTJOIN("|", true, E:E))))), 0, SUM(FILTER(INDIRECT(TEXT($C$2,"yyyy-mm")&"記録!C:C"), (INDIRECT(TEXT($C$2,"yyyy-mm")&"記録!B:B")=B5) * (REGEXMATCH(INDIRECT(TEXT($C$2,"yyyy-mm")&"記録!D:D"), TEXTJOIN("|", true, E:E))))))

…かなーり長い式になりましたが、同じような集計ができますし、シートを月ごとに変割った時に C2 セルの箇所を変更すれば、その月の集計ができる、という形で対応出来ました。

まとめ

  • 生成 AI を使うと google で調べる手間が省けました。
  • 改めて時間があった時に上記の内容を google で調べたのですが4時間ほどかかってもあまり成果が得られず。
  • (おまけで使った)INDIRECTは Excel やスプレッドシートを使う上で非常に便利です。

アノテーション株式会社について

アノテーション株式会社はクラスメソッドグループのオペレーション専門特化企業です。

サポート・運用・開発保守・情シス・バックオフィスの専門チームが、最新 IT テクノロジー、高い技術力、蓄積されたノウハウをフル活用し、お客様の課題解決を行っています。

当社は様々な職種でメンバーを募集しています。

「オペレーション・エクセレンス」と「らしく働く、らしく生きる」を共に実現するカルチャー・しくみ・働き方にご興味がある方は、アノテーション株式会社 採用サイトをぜひご覧ください。

Share this article

facebook logohatena logotwitter logo

© Classmethod, Inc. All rights reserved.